package com.mms.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mms.entity.Invitation;
import com.mms.entity.InvitationId;
import com.mms.entity.InvitationMessage;
import com.mms.entity.Meeting;
import com.mms.entity.Message;
import com.mms.entity.Staff;
import com.mms.util.HibernateUtil;

public class InvitationDao {

    
    public boolean isInvitationExist(Invitation invitation){
        boolean isExist = false;
           List<BigInteger> result ;
           int num = 0;
           Session session = HibernateUtil.getSession(); 
           try{ 
               String sql = "select count(*) from invitation where s_id = ? and topic = ?";              
               Transaction transaction=session.beginTransaction();
               Query query=session.createSQLQuery(sql);
               query.setInteger(0,invitation.getSid());
               query.setString(1,invitation.getTopic());
               result=query.list();
               num=Integer.parseInt(result.get(0).toString());
               if (num>0)  isExist=true;
               transaction.commit();
           }catch(Exception ex){ 
               ex.printStackTrace(); 
           }finally{ 
           session.close(); 
           }
           return isExist;
   }
    
    public boolean addInvitation(Invitation invitation){
           Session session = HibernateUtil.getSession(); 
           try{ 
           Transaction transaction=session.beginTransaction();
           session.save(invitation); 
           transaction.commit();
           return true; 
           }catch(Exception ex){ 
               ex.printStackTrace();
           return false; 
           }finally{ 
           session.close(); 
           } 
   }
    
    
    public int getNewInvitationMessageCountByStaffID(int sID) {
        List<BigInteger> result ;
        int num = 0;
        Session session = HibernateUtil.getSession(); 
       try{
           String sql = "select count(*) from invitation where check_flag=0 and s_id = ?";
           Transaction transaction=session.beginTransaction();
           Query query=session.createSQLQuery(sql);
           query.setInteger(0,sID);
           result=query.list();
           num=Integer.parseInt(result.get(0).toString());
       }catch(Exception e){
           e.printStackTrace();
       }
       finally{
           session.close();
       }
       return num;
        
    }
    
    public List<InvitationMessage> getInvitationByStaffID(int sID) {
        List<Object[]> objs;
        List<InvitationMessage> invitationMessages=new ArrayList<InvitationMessage>();
        Session session = HibernateUtil.getSession(); 
       try{
           String sql = "select invitation.topic,start_time,end_time,mr_name from invitation,meeting,meeting_room where invitation.topic=meeting.topic and meeting.mr_id=meeting_room.mr_id and invitation.s_id = ?";
           Transaction transaction=session.beginTransaction();
           Query query=session.createSQLQuery(sql);
           query.setInteger(0, sID);
           objs=query.list();
           for (Object[] objects : objs) {
               InvitationMessage message=new InvitationMessage();
               message.setTopic(objects[0].toString());
               message.setStartTime(objects[1].toString());
               message.setEndTime(objects[2].toString());
               message.setMrName(objects[3].toString());
               invitationMessages.add(message);
           }
           transaction.commit();
       }catch(Exception e){
           e.printStackTrace();
       }
       finally{
           session.close();
       }
       return invitationMessages;
        
    }
    
    
    public void setInvitationRead(String topic,int sID) {
       Session session = HibernateUtil.getSession(); 
       try{ 
       Transaction transaction=session.beginTransaction();
       Staff staff=new Staff(sID);
       Meeting meeting=new Meeting();
       meeting.setTopic(topic);
       InvitationId invitationId=new InvitationId(staff, meeting);
       Invitation invitation=(Invitation)session.get(Invitation.class, invitationId);
       invitation.setCheckFlag(1);
       session.save(invitation);
       transaction.commit();
       }catch(Exception ex){ 
           ex.printStackTrace();
       }finally{ 
       session.close(); 
       } 
       
    }
    
}
